# -*- encoding: utf-8 -*-
import time, datetime
from collections import defaultdict, OrderedDict
from operator import itemgetter
from itertools import groupby
import math
from odoo import models, fields, api, _, tools, SUPERUSER_ID
#import odoo.addons.decimal_precision as dp
from odoo.tools import float_compare, float_round, float_is_zero
from odoo.exceptions import UserError, ValidationError
from odoo.addons import base_cw



class account_cash_monthly(models.Model):
    _name = 'account.cash.monthly'
    _description = u'出纳日记帐月汇总'

    period_id = fields.Many2one('account.period', u'期别')
    account_id = fields.Many2one('cncw.account', u'会计科目', ondelete="restrict")
    begin = fields.Float(u'期初金额', digits='Amount')
    debit = fields.Float(u'本期收入', digits='Amount')
    credit = fields.Float(u'本期支出', digits='Amount')
    balance = fields.Float(u'期末余额', digits='Amount')
    daily_line_ids = fields.One2many('account.cash.dialy.line', 'cash_monthly_id', u'日记帐明细')

    year_debit = fields.Float(u'年累收入', digits='Amount')
    year_credit = fields.Float(u'年累支出', digits='Amount')

    # 日记账月结
    def init(self):
        self._cr.execute("""
        CREATE OR REPLACE FUNCTION public.account_cash_monthly_settle(
        period_id_t integer,
        pre_period_id_t integer,
        uid integer)
        RETURNS void AS
            $BODY$

                DECLARE
                    begin_amount_t numeric;
                    end_t numeric;
                    rec record;
                    rec_balance record;
                    rec_monthly record;
                    seq  integer;
            begin
                 create temp table account_cash_dialy_line_t(
                     id integer, -- 明细id
                     account_id integer, -- 会计科目
                     cash_monthly_id integer,
                     sub_account_id integer,
                     debit numeric, -- 本期借方(原币)
                     credit numeric, -- 本期贷方(原币)
                     dc_type character varying,
                     report_seq integer,
                     balance numeric, -- 期末余额(原币)
                     date date
                 );
                 create temp table account_cash_monthly_t(
                     account_id integer, -- 会计科目
                     debit numeric, -- 本期借方(原币)
                     credit numeric, -- 本期贷方(原币)
                     begin numeric, -- 本期贷方(原币)
                     balance numeric -- 本期贷方(原币)
                 );

                 delete from account_cash_monthly where period_id=period_id_t;
-- 创建 中间表，取所有该期别 日记账明细
                 insert into account_cash_dialy_line_t(id,account_id,sub_account_id,debit,credit,dc_type,report_seq,balance,date)
                       select a.id,b.account_id,a.sub_account_id,a.debit,a.credit,a.dc_type,0,0,b.date
                      from account_cash_dialy_line a left join account_cash_dialy b on a.master_id=b.id
                        where b.state='audited'
                          and b.period_id=period_id_t
                        order by b.date,a.id;
                  
                  insert into account_cash_dialy_line_t(id,account_id,sub_account_id,debit,credit,dc_type,report_seq,balance,date)
                      select a.id,b.account_id,a.sub_account_id,a.debit,a.credit,a.dc_type,0,0,b.date
                      from account_voucher_line a left join account_voucher b on a.master_id=b.id
                       where b.state='audited'
                        and b.period_id=period_id_t
                        order by b.date,a.id;

                 insert into account_cash_monthly_t(account_id)
                        select a.id
                        from cncw_account a
                        left join subaccount_category b on b.id=a.subaccount_category_id
                        where b.name='现金流项目';

                 --insert into account_cash_monthly_t(account_id,debit,credit)
                 --       select account_id,
                 --              sum(coalesce(debit,0)) as debit, -- 本期借
                 --              sum(coalesce(credit,0)) as credit -- 本期贷
                 --      from account_cash_dialy_line_t
                 --       group by account_id;

                 --insert into account_cash_monthly_t(account_id)
                 --       select b.account_id
                 --       from account_cash_monthly b
                 --       left join account_cash_monthly_t c on c.account_id != b.account_id
                 --      where b.period_id=pre_period_id_t;

                 update account_cash_monthly_t a set debit=b.debit,credit=b.credit
                        from (select account_id,
                                     sum(coalesce(debit,0)) as debit, -- 本期借
                                     sum(coalesce(credit,0)) as credit -- 本期贷
                        from account_cash_dialy_line_t
                        group by account_id) b
                        where a.account_id=b.account_id;

                 update account_cash_monthly_t a set begin=coalesce(b.begin,0)
                 from(
                     select coalesce(balance,0) as begin,account_id
                     from account_cash_monthly
                     where period_id=pre_period_id_t) b
                 where a.account_id=b.account_id;


-- 创建汇总主档（期初=科目余额表上期 期末）
                 insert into account_cash_monthly(begin,debit,credit,balance,account_id,period_id)
                        select coalesce(begin,0) as begin,debit,credit,
                               coalesce(begin,0) + debit-credit as balance,
                               account_id,period_id_t as period_id
                        from account_cash_monthly_t;


-- 循环 汇总主档（按期别，会计科目）
            for rec_balance in select a.id as cash_monthly_id,a.account_id,a.period_id,a.begin as begin_t
                      from account_cash_monthly a 
                      where period_id=period_id_t

                  loop
                      seq:=0;
                      begin_amount_t:=rec_balance.begin_t;
                      seq:=seq+1;
       -- 循环每一笔 日期账明细，计算每笔  余额
                      for rec in select id,account_id,sub_account_id,debit,credit,dc_type
                          from account_cash_dialy_line_t a
                          where account_id=rec_balance.account_id
                          order by a.date,id
                      loop
                          end_t:=coalesce(begin_amount_t,0)+(coalesce(rec.debit,0)-coalesce(rec.credit,0));

                          update account_cash_dialy_line_t set report_seq=seq,balance=end_t,cash_monthly_id=rec_balance.cash_monthly_id
                              where id=rec.id;

                          seq:=seq+1;
                          begin_amount_t:=end_t;

                          end loop;
                  end loop;

                  update account_cash_dialy_line a set report_seq=b.report_seq,balance=b.balance,cash_monthly_id=b.cash_monthly_id,date=b.date
                  from account_cash_dialy_line_t b
                  where a.id=b.id;
                  
                  update account_voucher_line a set cash_seq=b.report_seq,cash_balance=b.balance,cash_monthly_id=b.cash_monthly_id
                  from account_cash_dialy_line_t b
                  where a.id=b.id;
                  drop  table account_cash_dialy_line_t;
                  drop  table account_cash_monthly_t;

            end;
        $BODY$
        LANGUAGE plpgsql VOLATILE
        COST 100;
""")
